<?php
// 数据库配置
\$db_config = array(
    'host' => 'localhost',
    'port' => '3306',
    'dbname' => 'test',
    'username' => 'root',
    'password' => 'root',
    'charset' => 'utf8mb4',
);

\$table_prefix = 'ps_';
//\$prov_tb = \$table_prefix.'province';
//\$city_tb = \$table_prefix.'city';
//\$area_tb = \$table_prefix.'area';
\$street_tb = \$table_prefix . 'street';

\$root = 'H:/java/marketingplatform/src/test';
// 从文件读取区域数据
//\$prov_data = file_get_contents(\$root.'/dist/provinces.json');
//\$city_data = file_get_contents(\$root.'/dist/cities.json');
//\$area_data = file_get_contents(\$root.'/dist/areas.json');
\$street_data = file_get_contents(\$root . '/dist/streets.json');

//\$prov_array = json_decode(\$prov_data, true);
//unset(\$prov_data);
//\$city_array = json_decode(\$city_data, true);
//unset(\$city_data);
//\$area_array = json_decode(\$area_data, true);
//unset(\$area_data);
\$street_array = json_decode(\$street_data, true);
unset(\$street_data);

// 初始化数据库连接
try {
    \$dbh = new PDO("mysql:host={\$db_config['host']};port={\$db_config['port']};dbname={\$db_config['dbname']};charset={\$db_config['charset']}",
        \$db_config['username'], \$db_config['password']);
} catch (Exception \$e) {
    echo '数据库链接失败：', \$e->getMessage();
    exit();
}


// 表格创建SQL
\$table_create_sql = <<<SQL
CREATE TABLE IF NOT EXISTS `{\$street_tb}` (
`code` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`parent_code` int(11) UNSIGNED NOT NULL,
PRIMARY KEY(`code`), KEY(`parent_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='街道';
SQL;
// 执行表格创建
\$dbh->exec(\$table_create_sql);
// 街道数据添加
\$street_data_insert_sql = "INSERT INTO `{\$street_tb}` (`code`, `name`, `parent_code`) VALUES ";
// 省份数据删除

// 街道数据删除
\$street_delete_sql = "DELETE FROM `{\$street_tb}`";
// 省市区数据组装

\$values = array();
foreach (\$street_array as \$street) {
//    print  \$street['name'];
    \$values[] = "({\$street['code']}, '{\$street['name']}', '{\$street['parent_code']}')";
}
\$street_data_insert_sql .= implode(',', \$values) . ';';
try {
    \$dbh->beginTransaction();
    \$dbh->exec(\$street_delete_sql);
//    \$dbh->exec(\$street_data_insert_sql);
    \$dbh->commit();
} catch (Exception \$e) {
    \$dbh->rollBack();
    echo '导入数据失败：', \$e->getMessage();
}

\$dbh = null;